package com.ego.dao.impl;

import com.ego.dao.CartDao;
import com.ego.dao.OrderDao;
import com.ego.pojo.CartItem;
import com.ego.pojo.Order;
import com.ego.pojo.OrderItem;
import com.ego.utils.JDBCUtils;
import com.ego.vo.OrderItemVo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 袁梦达 2019012364
 */
public class OrderDaoImpl implements OrderDao {

    private CartDao cartDao = new CartDaoImpl();

    //展示订单列表
    @Override
    public List<Order> showOrder(String userNumber){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            List<Order> orderList = new ArrayList<>();
            String sql = "SELECT * FROM _order WHERE user_number = ? ORDER BY order_time DESC";
            statement = connection.prepareStatement(sql);
            statement.setString(1, userNumber);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                Order order = new Order();
                order.setUserNumber(userNumber);
                order.setOrderId(resultSet.getInt("id"));
                String time = (resultSet.getTimestamp("order_time") + "");
                time = time.substring(0, time.length() - 2);
                order.setOrderTime(time);
                order.setQuantity(resultSet.getInt("quantity"));
                order.setSumPrice(resultSet.getDouble("sum_price"));
                orderList.add(order);
            }
            return orderList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return null;
    }

    //展示某个订单项的详情
    @Override
    public List<OrderItemVo> showOrderDetail(Integer orderItemId){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        List<OrderItemVo> orderItemVoList = new ArrayList<>();
        try {
            String sql = "SELECT * FROM order_item, product WHERE order_item.product_id = product.id AND order_item.order_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, orderItemId);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                OrderItemVo orderItemVo = new OrderItemVo();
                orderItemVo.setProductId(resultSet.getInt("product_id"));
                orderItemVo.setImage(resultSet.getString("image"));
                orderItemVo.setBaseInformation(resultSet.getString("base_information"));
                orderItemVo.setPrice(resultSet.getDouble("price"));
                orderItemVo.setQuantity(resultSet.getInt("product_quantity"));
                orderItemVo.setSumPrice(resultSet.getDouble("sum_price"));
                orderItemVoList.add(orderItemVo);
            }
            return orderItemVoList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return null;
    }


    //生成订单
    @Override
    public boolean createOrder(String userNumber){

        //生成新的订单号
        Integer orderId = countOrder() + 1;

        //创建新订单
        boolean result = createNewOrder(orderId, userNumber);
        if(!result){
            return false;
        }

        Order order = new Order(orderId, userNumber, new java.util.Date().toString(), 0, 0);

        Integer cartId = cartDao.getCartByUserNumber(userNumber);
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "select * from cart_product where cart_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, cartId);
            ResultSet resultSet = statement.executeQuery();

            int orderItemId = countOrderItem();
            while (resultSet.next()){
                OrderItem orderItem = new OrderItem();
                orderItemId++;
                orderItem.setOrderItemId(orderItemId);
                orderItem.setOrderId(orderId);
                Integer productId = resultSet.getInt("product_id");
                orderItem.setProductId(productId);
                orderItem.setQuantity(resultSet.getInt("quantity"));
                orderItem.setSumPrice(getPriceByProductId(productId) * resultSet.getInt("quantity"));

                //将每个orderItem存入order_item表中
                boolean b = insertOrderItem(orderItem);

                Integer quantity = order.getQuantity() + orderItem.getQuantity();
                double sumPrice = order.getSumPrice() + orderItem.getSumPrice();
                order.setQuantity(quantity);
                order.setSumPrice(sumPrice);

            }
            //生成Order，并存入_order表中
            insertOrder(order);

            //删除购物车中cartItem
            deleteCartProduct(cartId);

            //将购物车的总价改为0
            makeCartSumPriceToZero(cartId);

            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return false;
    }

    private boolean makeCartSumPriceToZero(Integer cartId){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "UPDATE cart SET sum_price = 0 WHERE id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, cartId);
            int rowCount = statement.executeUpdate();
            if(rowCount > 0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return false;
    }

    //先创建一个新订单（不插入数据）
    private boolean createNewOrder(Integer orderId, String userNumber){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "INSERT INTO _order VALUES(?, ?, now(), ?, ?)";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, orderId);
            statement.setString(2, userNumber);
            statement.setInt(3, 0);
            statement.setDouble(4, 0);
            int rowCount = statement.executeUpdate();
            if(rowCount > 0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return false;
    }

    private double getPriceByProductId(Integer productId){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "select price from product where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, productId);
            ResultSet resultSet = statement.executeQuery();
            if(resultSet.next()){
                return resultSet.getDouble("price");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return 0;
    }

    //查询order_item表有多少项
    private Integer countOrderItem(){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "select count(*) from order_item";
            statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            if(resultSet.next()){
                return resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return null;
    }

    //将每个orderItem存入order_item表中
    private boolean insertOrderItem(OrderItem orderItem){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "insert into order_item values(?, ?, ?, ?, ?)";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, orderItem.getOrderItemId());
            statement.setInt(2, orderItem.getOrderId());
            statement.setInt(3, orderItem.getProductId());
            statement.setInt(4, orderItem.getQuantity());
            statement.setDouble(5, orderItem.getSumPrice());

            int rowCount = statement.executeUpdate();
            if(rowCount > 0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return false;
    }

    //将Order存入_order表中
    private boolean insertOrder(Order order){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "update _order set quantity = ?, sum_price = ? where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, order.getQuantity());
            statement.setDouble(2, order.getSumPrice());
            statement.setInt(3, order.getOrderId());

            int rowCount = statement.executeUpdate();
            if(rowCount > 0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return false;
    }

    //删除cart_product中数据
    private boolean deleteCartProduct(Integer cartId){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "delete from cart_product where cart_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, cartId);
            int rowCount = statement.executeUpdate();
            if(rowCount > 0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return false;
    }

    //查询_order表有多少项
    private Integer countOrder(){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement statement = null;
        try {
            String sql = "select count(*) from _order";
            statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            if(resultSet.next()){
                return resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(statement, connection);
        }
        return null;
    }
}
